#coding: utf8
import datetime
import MySQLdb
from MySQLdb import IntegrityError
from fresh.items import RestaurantItem
from fresh.items import RestaurantReview
from fresh.utils import Utils
from fresh.utils import GoogleAddressAttr
from fresh.utils import DatabaseDetails
from scrapy import log
import re
from pygeocoder import Geocoder
import time


class TripMasterDB(object):

    """
    host = "mysql1.alwaysdata.com"
    user = "62465_jmgost"
    passw = "sygnium"
    skema = "freshbuzzing_1"
    
    
    host = "localhost"
    user = "root"
    passw = "password"
    skema = "fresh"
    """


    utils = None

    logLocalName="TRIP_MAST_SCRAPPY"

    INSERT="INSERT"
    UPDATE="UPDATE"
    ACTIVE='Y'
    NACTIVE='N'    


    def getCursor(self):
        try:
            self.cursor = self.db.cursor()
            self.cursor.execute('SET NAMES utf8;')
            self.cursor.execute('SET CHARACTER SET utf8;')
            self.cursor.execute('SET character_set_connection=utf8;')
        except Exception,e:
            self.utils.logProcessDetail("Error al crear cursor MySQLdb:  " + str(e), self.utils.ERROR)
            #try to reconnect
            self.connectDB()
    

    def connectDB(self):
        try:
            props = DatabaseDetails()
            self.db = MySQLdb.connect(props.host,props.user,props.passw,props.skema)
            self.db.set_character_set('utf8')
        except Exception,e:
            self.utils.logProcessDetail("Error al conectar a base de datos MySQLdb.connect: " + str(e), self.utils.ERROR)
            return False
        return True


    def readURLs(self,urls):

        """read few restaurants each time"""
        limit='LIMIT 2500'
        
        #sql = "SELECT DS_URL_TRIPADVISOR,ID_REST_SOURCE FROM MAST_REST_TRIPADVISOR WHERE  id_restaurant=7833"

        sql = "SELECT DS_URL_TRIPADVISOR,ID_REST_SOURCE "
        sql += "FROM MGM_MANUAL_LINKS T1 INNER JOIN MAST_REST_TRIPADVISOR T2 " 
        sql += "ON T1.ID_REST_SOURCE_REF = T2.ID_REST_SOURCE ";
        sql += "WHERE T1.DS_STAtUS = 'NOT PROCESSED' AND T1.ID_WEB_REVIEWS_REF = 'TRIPADVISOR'";

        try:
            self.getCursor()
            self.cursor.execute(sql)
            for row in self.cursor.fetchall():
                urls.append(row[0])
                
            self.db.commit()

               
        except Exception,e:
            self.utils.logProcessDetail("Error al ejecutar sentencia SQL en readURLs:  " + str(e), self.utils.ERROR)
            self.db.rollback()
        else:
            self.db.commit()
        finally:
            self.cursor.close()

        sql0="SELECT DS_URL_TRIPADVISOR,ID_REST_SOURCE FROM MAST_REST_TRIPADVISOR WHERE id_restaurant is not null " 
        sql1= "order by DT_LAST_UPDATED asc %s" % (limit)
        sql = sql0+sql1
        
        
   

        try:
            self.getCursor()
            self.cursor.execute(sql)
            for row in self.cursor.fetchall():
                urls.append(row[0])
                
            self.db.commit()

               
        except Exception,e:
            self.utils.logProcessDetail("Error al ejecutar sentencia SQL en readURLs:  " + str(e), self.utils.ERROR)
            self.db.rollback()
        else:
            self.db.commit()
        finally:
            self.cursor.close()

    def getRestFromUrl(self,url):
        idRest=None
        dtPost=None
        #sql="SELECT ID_REST_SOURCE, DS_URL_TRIPADVISOR FROM MAST_REST_TRIPADVISOR WHERE DS_URL_TRIPADVISOR = %s "
        sql="SELECT mr.ID_REST_SOURCE, mr.DS_URL_TRIPADVISOR, hp.DT_POST FROM MAST_REST_TRIPADVISOR mr left join HIST_POSTS_TRIPADVISOR hp on mr.ID_REST_SOURCE=hp.ID_REST_SOURCE WHERE mr.DS_URL_TRIPADVISOR=%s order by hp.DT_POST DESC limit 1"


        try:
            self.getCursor()
            self.cursor.execute(sql,(url))
            data=self.cursor.fetchone();
            if data is not None:
                idRest=str(data[0])
                dtPost=data[2]
            else:
                self.utils.logProcessDetail("getRestFromUrl CAN NOT GET RESTAURANT CODE FROM RESTAURANT URL: " + str(url), self.utils.ERROR)
                
            
        except Exception,e:
            self.utils.logProcessDetail("Error: " + str(e) + " al getRestFromUrl SQL: " + str(sql), self.utils.ERROR)
            self.db.rollback()
        else:
            self.db.commit()
        finally:
            self.cursor.close()
            return (idRest,dtPost)



    def insertRestaurants(self,restaurant,insupdate):
        try:
            sql=""
            db_id_rest=None
            db_lat=None
            db_long=None
            db_id_rest_mast=None
            db_rest_name=None
            db_bLockAddress=None
            db_bLockName=None
            db_address=None
            db_address_format=None
            db_address_google=None
            db_country=None
            db_AAL1=None
            db_AAL2=None
            db_locality=None
            db_cp=None
            db_location=None

            googleAttr = GoogleAddressAttr()

            if insupdate==self.INSERT:
                sql = "SELECT ID_REST_SOURCE, NUM_REST_LATITUDE, NUM_REST_LONGITUDE,ID_RESTAURANT,DS_REST_NAME,BL_LOCK_ADDRESS, BL_LOCK_NAME,DS_REST_ADDRESS,DS_REST_ADDRESS_FORMATTED, DS_REST_ADDRESS_GOOGLE,ID_COUNTRY_3, ID_ADMIN_AREA_L1, ID_ADMIN_AREA_L2, ID_LOCALITY, ID_ZIP_CODE,  AUX_DS_GOOGLE_LOCATION FROM MAST_REST_TRIPADVISOR WHERE DS_REST_NAME=%s and  DS_REST_ADDRESS=%s"
                self.getCursor()
                self.cursor.execute(sql,(self.utils.convertListToString(restaurant['restaurant_name']),self.utils.convertListToString(restaurant['address'])))
            elif insupdate==self.UPDATE:
                """when just update the ID_REST_SOURCE in restaurant is filled in"""
                sql = "SELECT ID_REST_SOURCE, NUM_REST_LATITUDE, NUM_REST_LONGITUDE,ID_RESTAURANT,DS_REST_NAME,BL_LOCK_ADDRESS, BL_LOCK_NAME,DS_REST_ADDRESS,DS_REST_ADDRESS_FORMATTED, DS_REST_ADDRESS_GOOGLE,ID_COUNTRY_3, ID_ADMIN_AREA_L1, ID_ADMIN_AREA_L2, ID_LOCALITY, ID_ZIP_CODE,  AUX_DS_GOOGLE_LOCATION FROM MAST_REST_TRIPADVISOR WHERE ID_REST_SOURCE=%s"
                self.getCursor()
                self.cursor.execute(sql,(restaurant['idRestSource']))
            else:
                self.utils.logProcessDetail("Error de programación!!, insertRestaurants llamado con parametro insupdate no valido", self.utils.ERROR)
                return

            
            data=self.cursor.fetchone();
            if data is not None:
                """insert turns to update"""
                if insupdate==self.INSERT:
                    insupdate=self.UPDATE

                
                #self.utils.log("DEBUG", "restaurant: " + str(restaurant),self.utils.DEBUG)
                db_id_rest=data[0]
                db_lat=data[1]
                db_long=data[2]
                db_id_rest_mast=data[3]
                db_rest_name=data[4]
                db_bLockAddress=data[5]
                db_bLockName=data[6]
                db_address=data[7]
                db_address_format=data[8]
                db_address_google=data[9]
                
                db_country=data[10]
                db_AAL1=data[11]
                db_AAL2=data[12]
                db_locality=data[13]
                db_cp=data[14]
                db_location=data[15]
                
                
                """before any action let's check there's data from the scrappy, if not let's deactivate the restaurant"""
                if len(self.utils.cleanString(self.utils.convertListToString(restaurant['restaurant_name'])))==0:
                    self.setRestaurantStatus(db_id_rest,self.NACTIVE)
                    return
                else:
                    self.setRestaurantStatus(db_id_rest,self.ACTIVE)

                self.utils.scrapyQA(restaurant)
                
                if db_bLockAddress==self.utils.IND_DB_YES:
                    if db_bLockName==self.utils.IND_DB_YES:
                        """ALL BLOCKED--> copy DB data into record, so no overwritting takes place"""
                        googleAttr.address_format=db_address_format
                        googleAttr.lat=db_lat
                        googleAttr.long=db_long
                        googleAttr.location=db_location
                        
                        googleAttr.country_3=db_country
                        googleAttr.admin_area_L1=db_AAL1
                        googleAttr.admin_area_L2=db_AAL2
                        googleAttr.locality=db_locality
                        googleAttr.zip=db_cp

                        restaurant['restaurant_name']=db_rest_name
                        restaurant['address_google']=db_address_google
                        restaurant['address']=db_address
                        #restaurant['cp']=db_cp
                        #restaurant['city']=db_city                        
                    else:
                        """ADDRESS BLOCKED, NOT NAME-->copy DB data into record, so no overwritting takes place"""
                        googleAttr.address_format=db_address_format
                        googleAttr.lat=db_lat
                        googleAttr.long=db_long
                        googleAttr.location=db_location

                        googleAttr.country_3=db_country
                        googleAttr.admin_area_L1=db_AAL1
                        googleAttr.admin_area_L2=db_AAL2
                        googleAttr.locality=db_locality
                        googleAttr.zip=db_cp

                        """restaurant['restaurant_name']=db_rest_name"""
                        restaurant['address_google']=db_address_google
                        restaurant['address']=db_address
                        #restaurant['cp']=db_cp
                        #restaurant['city']=db_city
                        
                else:
                    """ADDRESS NOT BLOCKED --> let's get google info if necessary"""
                    """before fill-in data to avoid additional if-else"""
                    googleAttr.address_format=db_address_format
                    googleAttr.lat=db_lat
                    googleAttr.long=db_long
                    googleAttr.location=db_location

                    googleAttr.country_3=db_country
                    googleAttr.admin_area_L1=db_AAL1
                    googleAttr.admin_area_L2=db_AAL2
                    googleAttr.locality=db_locality
                    googleAttr.zip=db_cp
                    
                    if db_lat is not None and db_long is not None:
                        if db_lat == 0 or db_long == 0:
                            self.utils.getGoogleCoordinates(self.utils.convertListToString(restaurant['address_google']).encode( "utf-8" ),"",googleAttr)
                        else:
                            """in case address changed it is necessary to calculate coordenates again too"""
                            if db_address!=self.utils.convertListToString(restaurant['address']):
                                self.utils.getGoogleCoordinates(self.utils.convertListToString(restaurant['address_google']).encode( "utf-8" ),"",googleAttr)
                    else:
                        """coordinates not defiend, let's get them"""
                        self.utils.getGoogleCoordinates(self.utils.convertListToString(restaurant['address_google']).encode( "utf-8" ),"",googleAttr)
                        self.utils.log("DEBUG","coordinates: "+ str(googleAttr),self.utils.DEBUG)
                        
                    if db_bLockName==self.utils.IND_DB_YES:
                        """ADDRESS NOT BLOCKED, NAME BLOCKED--> copy db name into record"""
                        restaurant['restaurant_name']=db_rest_name                                
            else:
                if insupdate==self.UPDATE:
                    self.utils.logProcessDetail("SE SUPONIA QUE TENIAMOS QUE HACER UPDATE DE RESTAURANTE PERO NO SE ENCUENTRA EL RESTAURANTE CON idRestSource: " + str(restaurant), self.utils.ERROR)
                    return
                else:
                    self.utils.getGoogleCoordinates(self.utils.convertListToString(restaurant['address_google']).encode( "utf-8" ),"",googleAttr)
                
                    

            """-----------------------------------------------------------------"""
            """ All data properly set up taking into consideration blocked data"""
            """-----------------------------------------------------------------"""
            if insupdate==self.INSERT:            
                self.utils.log("DEBUG", "VAMOS A INSERTAR" + self.utils.convertListToString(restaurant['restaurant_name']),self.utils.DEBUG)
                sql = "INSERT INTO MAST_REST_TRIPADVISOR (DS_REST_ADDRESS_FORMATTED, DS_REST_NAME, DS_REST_ADDRESS_GOOGLE, AUX_DS_GOOGLE_LOCATION,DS_REST_ADDRESS, ID_COUNTRY_3, ID_ADMIN_AREA_L1, ID_ADMIN_AREA_L2, ID_LOCALITY,ID_ZIP_CODE, NUM_REST_LATITUDE, NUM_REST_LONGITUDE, DS_URL_TRIPADVISOR, DS_URL_REST, NUM_TELEPHONE, NUM_SCORE, NUM_POSTS, NUM_SCORE_CUISINE, NUM_SCORE_AMBIENT, NUM_SCORE_VALUE_PRICE, NUM_SCORE_SERVICE, DT_LAST_UPDATED) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,NOW())" 
                self.cursor.execute(sql,(googleAttr.address_format,self.utils.convertListToString(restaurant['restaurant_name']),self.utils.convertListToString(restaurant['address_google']),googleAttr.location,self.utils.convertListToString(restaurant['address']),googleAttr.country_3,googleAttr.admin_area_L1,googleAttr.admin_area_L2,googleAttr.locality,googleAttr.zip,googleAttr.lat,googleAttr.long,self.utils.convertListToString(restaurant['url_scrapy']),self.utils.convertListToString(restaurant['url_rest']),self.utils.convertListToString(restaurant['phone']),self.utils.convertListToString(restaurant['score']),self.utils.convertListToString(restaurant['reviews_total']),self.utils.convertListToString(restaurant['score_comida']),self.utils.convertListToString(restaurant['score_atmosfera']),self.utils.convertListToString(restaurant['score_calidadP']),self.utils.convertListToString(restaurant['score_servicio'])))
                self.db.commit()

                self.cursor.execute('SELECT LAST_INSERT_ID()')
                data=self.cursor.fetchone();
                db_id_rest=data[0]         
            elif insupdate==self.UPDATE:
                try:
                    """update all"""
                    self.utils.log("DEBUG","VAMOS A UPDATE ALL" + self.utils.convertListToString(restaurant['restaurant_name']),self.utils.DEBUG)
                    sql = "UPDATE MAST_REST_TRIPADVISOR SET DS_REST_ADDRESS_FORMATTED=%s,DS_REST_ADDRESS=%s, DS_REST_NAME=%s,DS_REST_ADDRESS_GOOGLE=%s, AUX_DS_GOOGLE_LOCATION=%s, ID_COUNTRY_3=%s, ID_ADMIN_AREA_L1=%s, ID_ADMIN_AREA_L2=%s, ID_LOCALITY=%s,ID_ZIP_CODE=%s ,NUM_REST_LATITUDE=%s, NUM_REST_LONGITUDE=%s, DS_URL_TRIPADVISOR=%s,DS_URL_REST=%s,NUM_TELEPHONE=%s, NUM_SCORE=%s, NUM_POSTS=%s, NUM_SCORE_CUISINE=%s, NUM_SCORE_AMBIENT=%s, NUM_SCORE_VALUE_PRICE=%s, NUM_SCORE_SERVICE=%s, DT_LAST_UPDATED=NOW() WHERE ID_REST_SOURCE=%s"
                    self.cursor.execute(sql,(googleAttr.address_format,self.utils.convertListToString(restaurant['address']),self.utils.convertListToString(restaurant['restaurant_name']),self.utils.convertListToString(restaurant['address_google']),googleAttr.location,googleAttr.country_3,googleAttr.admin_area_L1,googleAttr.admin_area_L2,googleAttr.locality,googleAttr.zip,googleAttr.lat,googleAttr.long,self.utils.convertListToString(restaurant['url_scrapy']),self.utils.convertListToString(restaurant['url_rest']),self.utils.convertListToString(restaurant['phone']),self.utils.convertListToString(restaurant['score']),self.utils.convertListToString(restaurant['reviews_total']),self.utils.convertListToString(restaurant['score_comida']),self.utils.convertListToString(restaurant['score_atmosfera']),self.utils.convertListToString(restaurant['score_calidadP']),self.utils.convertListToString(restaurant['score_servicio']),str(db_id_rest)))
                    self.db.commit()
                except IntegrityError as e:
                    #that happens when url restaurant has been changed by trip (301), record warning but continue with posts inserts
                    self.utils.logProcessDetail("Integrity error: " + str(e)+ " al ejecutar sentencia SQL1: " + str(sql) + "RESTAURANT: " + str(restaurant) , self.utils.WARNING)
                    self.db.rollback()
                except Exception,e:
                    raise e
                    
            else:
                self.utils.logProcessDetail("Error de programación en __insertUpdateRestaurant variable insupdate con valor no posible: " +str(insupdate), self.utils.ERROR)
                return
            """------------- end insert/update restaurant---------------------------"""


            """-----------------------------------------------------------------------"""
            """Restaurant should be inserted, now let's insert relation between restaurants and its types"""
            """-----------------------------------------------------------------------"""
            """first get ids of type of restaurant from master of types"""
            lTypesRest = self.utils.getMasterTypesRestSource(self.utils.ROBOT_TRIP, restaurant['rest_type'])
            """delete previous relationships"""
            sql = "DELETE FROM REL_TYPE_REST_SOURCE WHERE ID_WEB_REVIEW=%s and ID_REST_SOURCE=%s"
            self.cursor.execute(sql,(self.utils.ROBOT_TRIP,str(db_id_rest)))
            """insert current relationships"""
            for restType in lTypesRest:
                sql = "INSERT INTO REL_TYPE_REST_SOURCE (ID_WEB_REVIEW, ID_REST_SOURCE, ID_TYPE_REST_SOURCE) VALUES (%s,%s,%s)"
                self.cursor.execute(sql,(self.utils.ROBOT_TRIP,str(db_id_rest),str(restType)))
                self.db.commit()
                
            """-----------------------------------------------------------------------"""
            """finally insert into hist_reviews"""
            """-----------------------------------------------------------------------"""
            sql="INSERT INTO HIST_REST_TRIPADVISOR (ID_RESTAURANT,ID_REST_SOURCE,DT_UPDATE, DS_REST_NAME, NUM_SCORE, NUM_POSTS, NUM_SCORE_CUISINE, NUM_SCORE_AMBIENT, NUM_SCORE_VALUE_PRICE, NUM_SCORE_SERVICE) VALUES(%s,%s,CURDATE(),%s,%s,%s,%s,%s,%s,%s) ON DUPLICATE KEY UPDATE NUM_SCORE=%s, NUM_POSTS=%s, NUM_SCORE_CUISINE=%s, NUM_SCORE_AMBIENT=%s, NUM_SCORE_VALUE_PRICE=%s, NUM_SCORE_SERVICE=%s,ID_RESTAURANT=%s"
            self.cursor.execute(sql,(db_id_rest_mast,str(db_id_rest),self.utils.convertListToString(restaurant['restaurant_name']),self.utils.convertListToString(restaurant['score']),self.utils.convertListToString(restaurant['reviews_total']),self.utils.convertListToString(restaurant['score_comida']),self.utils.convertListToString(restaurant['score_atmosfera']),self.utils.convertListToString(restaurant['score_calidadP']),self.utils.convertListToString(restaurant['score_servicio']),self.utils.convertListToString(restaurant['score']),self.utils.convertListToString(restaurant['reviews_total']),self.utils.convertListToString(restaurant['score_comida']),self.utils.convertListToString(restaurant['score_atmosfera']),self.utils.convertListToString(restaurant['score_calidadP']),self.utils.convertListToString(restaurant['score_servicio']),db_id_rest_mast))
            self.db.commit()
            """-----------------------------------------------------------------------"""

            """-----------------------------------------------------------------------"""
            """finally reviews"""
            """-----------------------------------------------------------------------"""
            if (len(restaurant['reviews'])>0):
                count = 1
                for r in restaurant['reviews']:
                    if r.get('date') is not None and len(r['date']) > 0:
                        date = datetime.datetime.strptime(r['date'],"%d/%m/%Y").date()
                    else:
                        date=None

                    sql = "SELECT COUNT(*) FROM HIST_POSTS_TRIPADVISOR WHERE ID_REST_SOURCE=%s and DT_POST=%s and IFNULL(DS_TITLE_POST,'NA')=%s and IFNULL(DS_POST,'NA')=%s and IFNULL(ID_USER,'NA')=%s and IFNULL(NUM_SCORE,'NA')=%s"
                    self.cursor.execute(sql,(str(db_id_rest),date,self.utils.convertNullToNA(self.utils.convertListToString(r['title'])),self.utils.convertNullToNA(self.utils.convertListToString(r['comment'])),self.utils.convertNullToNA(self.utils.convertListToString(r['author'])),self.utils.convertNullToNA(self.utils.convertListToString(r['score']))))
                    n = self.cursor.fetchone()
                    if n[0] == 0:
                        sql= "SELECT COUNT(*) FROM HIST_POSTS_TRIPADVISOR WHERE ID_REST_SOURCE=%s and DT_POST=%s and IFNULL(DS_TITLE_POST,'NA')=%s and IFNULL(DS_POST,'NA')=%s and IFNULL(ID_USER,'NA')=%s and IFNULL(NUM_SCORE,'NA')=%s" %((str(db_id_rest),date,self.utils.convertNullToNA(self.utils.convertListToString(r['title'])),self.utils.convertNullToNA(self.utils.convertListToString(r['comment'])),self.utils.convertNullToNA(self.utils.convertListToString(r['author'])),self.utils.convertNullToNA(self.utils.convertListToString(r['score']))))
                        try:
                            sql = "INSERT INTO HIST_POSTS_TRIPADVISOR (ID_RESTAURANT, ID_REST_SOURCE, DS_REST_NAME, DT_POST, NUM_SCORE,NUM_SCORE_CUISINE, NUM_SCORE_AMBIENT, NUM_SCORE_VALUE_PRICE, NUM_SCORE_SERVICE, ID_USER, DS_TITLE_POST, DS_POST,DT_CREATED) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,NOW())"
                            self.cursor.execute(sql,(db_id_rest_mast,str(db_id_rest),self.utils.convertListToString(restaurant['restaurant_name']),date,self.utils.convertListToString(r['score']),self.utils.convertListToString(r['score_comida']),self.utils.convertListToString(r['score_ambiente']),self.utils.convertListToString(r['score_calidadP']),self.utils.convertListToString(r['score_servicio']),self.utils.convertListToString(r['author']),self.utils.convertListToString(r['title']),self.utils.convertListToString(r['comment'])))
                            self.db.commit()
                            count=count+1
                        except Exception,e:
                            #for strange reason above count(*) does not work when there are <br> within comments
                            if str(e).find("Duplicate entry") ==-1:
                                raise e
                                
                
        except Exception,e:
            self.utils.logProcessDetail("Error: " + str(e)+ " al ejecutar sentencia SQL1: " + str(sql) + "RESTAURANT: " + str(restaurant) , self.utils.ERROR)
            self.db.rollback()
        else:
            self.db.commit()
        finally:
            self.cursor.close()


    def setRestaurantStatus(self,id_rest,status):

        if status==self.NACTIVE:
            self.utils.logProcessDetail("Desactivar Restaurante id_source: "+str(id_rest), self.utils.INFO)
            
        sql ="UPDATE  MAST_REST_TRIPADVISOR SET BL_ACTIVE=%s, DT_LAST_UPDATED=NOW() WHERE ID_REST_SOURCE=%s"
        try:
            self.cursor.execute(sql,( str(status),str(id_rest)))
        except Exception,e:
            """probably tried to use internal cursor which is not valid because function called from outside, design problem, cursor should be factory!!!"""
            try:
                self.getCursor()
                self.cursor.execute(sql,( str(status),str(id_rest)))
            except Exception,e:
                self.db.rollback()
                self.utils.logProcessDetail("Error al setRestaurantNoActive id: " +str(id_rest) + "Error: "+ str(e),self.utils.ERROR)
            else:
                self.db.commit()
            finally:
                self.cursor.close()
        else:
            self.db.commit()



    def __del__(self):
        self.db.close()
        super(TripMasterDB, self).__del__()
    